let tree_service = require("./tree_service")
let rights_handler = require("./rights_handler")
let db = null;

let set_db = function (d) {
	db = d;
	rights_handler.set_db(d);
}

//根据数据库字段信息转换
function transfer_field_info(original) {
	let info = {};
	info.type = original.Type.split('(')[0];
	info.length = undefined;
	if (original.Type.indexOf('(') > 0 && original.Type.indexOf(')') > 0) {
		info.length = Number(original.Type.split('(')[1].split(')')[0]);
	}
	info.required = (original.Null != "YES");
	return info;
}

let del = async function (data, login_info, req, res) {
	let user_id = login_info.user_id;
	let datamodel = data.datamodel; //数据模型
	let tablename = data.tablename; //数据模型对应数据库表
	let rights = await rights_handler.get_datamodel_data_rights(datamodel, user_id);
	if (!rights.删) {
		return (Error("没有此数据模型数据删除权限：ID " + datamodel));
	}
	if (tablename) {
		rights = await rights_handler.get_table_data_rights(tablename, user_id);
		if (!rights.删) {
			return (Error("没有此数据模型数据删除权限：ID " + tablename));
		}
	}
	let id = data.id; //操作的记录ID
	let ids = data.ids; //批量删除的记录
	let result;
	if (id) {
		result = await db.update("update ?? set is_deleted=1,update_user=? where id=?", [tablename, user_id, id]);
	}
	if (ids) {
		let sql_ids = [];
		for (let id of ids.split(",")) {
			sql_ids.push(db.escape(id));
		}
		let ids_sql = sql_ids.join(",");
		result = await db.update("update ?? set is_deleted=1,update_user=? where id in (" + ids_sql + ")", [tablename, user_id]);
	}
	return result;
}

let update = async function (data, login_info, req, res) {
	let user_id = login_info.user_id;
	let datamodel = data.datamodel; //数据模型
	let tablename = data.tablename; //数据模型对应数据库表
	let rights = await rights_handler.get_datamodel_data_rights(datamodel, user_id);
	if (!rights.改) {
		return (Error("没有此数据模型数据修改权限：ID " + datamodel));
	}
	rights = await rights_handler.get_table_data_rights(tablename, user_id);
	if (!rights.改) {
		return (Error("没有此数据模型数据修改权限：ID " + tablename));
	}
	let id = data.id; //操作的记录ID
	let rows = data.rowsDiff ? data.rowsDiff : data.rows; //rowsDiff里有标修改的字段，如果没有rowsDiff则全部存
	if (rows.length == 0) {
		return;
	}
	let usql = "";
	let params = [];
	rows.forEach(row => {
		let id = row.id;
		let values = {};
		for (let key in row) {
			if (key !== "id" && key[0] != "#" && db.systemfields.filter(f => f.name == key).length == 0) {
				values[key] = row[key] ? row[key] : null //空字符串转null,防止int等类型不接受空字符串
			}
		}
		if (Object.keys(values).length == 0) {
			return;
		}
		usql += " update ?? set ?,update_user=? where id=?;";
		params.push(tablename, values, user_id, id);
	})
	if (usql.length == 0) {
		return;
	}
	let result = await db.multiupdate(usql, params);
	return result;
}

let add = async function (data, login_info, req, res) {
	let user_id = login_info.user_id;
	let datamodel = data.datamodel; //数据模型
	let tablename = data.tablename; //数据模型对应数据库表
	let rights = await rights_handler.get_datamodel_data_rights(datamodel, user_id);
	if (!rights.增) {
		return (Error("没有此数据模型数据新增权限：ID " + datamodel)); return;
	}
	rights = await rights_handler.get_table_data_rights(tablename, user_id);
	if (!rights.增) {
		return (Error("没有此数据模型数据修改权限：ID " + datamodel));
	}
	let result = await db.add("insert into ?? SET ?,create_user=?", [tablename, data.rows[0], user_id]);
	return result;
}

//用id获取行
let getrow = async function (data, login_info, req, res) {
	let datamodel = data.datamodel; //数据模型
	let tablename = data.tablename; //数据模型对应数据库表
	let id = data.id; //记录ID
	let filter = [{
		key: "id",
		value: id,
		like: false
	}]
	let result = await getrows({ filter: filter, datamodel: datamodel, tablename: tablename }, login_info, req, res);
	if (result.rows && result.rows.length > 0) {
		return (result.rows[0])
	}
	else {
		return null;
	}
}

let handle_composite_filter = function (f, related_fields) {
	if (f.conjunction && f.children) { //复合条件
		for (let child of f.children) {
			handle_composite_filter(child);
		}
	}
	else if (f.left && f.right && f.op) {
		f.prefix = 't'; //默认是主表t
		for (let i in related_fields) {
			let r_f = related_fields[i];
			if (f.left.field == "#" + r_f.this_field) {
				f.prefix = "p" + i;
				f.key = r_f.that_label_field;
			}
		}
	}
}

let getrows = async function (data, login_info, req, res) {
	let datamodel = data.datamodel; //数据模型
	let tablename = data.tablename; //数据模型
	let leveled = data.leveled; //数据是否有层级关系
	let leveled_sub_mode = data.leveled_sub_mode; //层级关系时 是否也单列查出来的子树
	let user_id = login_info.user_id;
	let rights = await rights_handler.get_datamodel_data_rights(datamodel, user_id);
	if (!rights.查) {
		return (Error("没有此数据模型数据查看权限：ID " + datamodel));
	}
	if (tablename) {
		rights = await rights_handler.get_table_data_rights(tablename, user_id);
		if (!rights.查) {
			return (Error("没有此数据模型数据修改权限：ID " + datamodel));
		}
	}
	let result = await db.query("select * from sys_datamodel where id=?", [datamodel]);
	let rows = result.rows;
	if (rows.length == 0) { return (Error('不存在这个datamodel:' + datamodel)); }
	else {
		let is_leveled = false;
		let leveled_set = {};
		let tablename = rows[0].tablename;
		let searchdata = data;
		if (!searchdata.filter) searchdata.filter = [];
		let filter = searchdata.filter;
		let keywords_search_columns = searchdata.keywords_search_columns;
		//附加删除字段
		filter.push({ key: "is_deleted", value: 1, not: true });
		//附加数据权限过滤
		if (rights.查 && rights.查.row_conditions) {
			for (let cond of rights.查.row_conditions) {
				let value = cond.value.replace(eval("/\\[current_user\\]/g"), user_id)
				filter.push({ key: cond.field, value: value, like: false });
			}
		}
		//主表加t前缀
		filter.forEach(f => {
			if (f.key && !f.conjunction) {
				f.prefix = 't'
			}
		});
		if (keywords_search_columns) keywords_search_columns.forEach(c => c.prefix = 't');
		//关联表处理
		let config = JSON.parse(rows[0].config);
		let related_fields = config.fields.concat(db.systemfields).filter(f => f.related);
		let selects = "t.*";
		let joins = "";
		for (let i in related_fields) {
			let f = related_fields[i];
			let this_field = f.name;
			let r_table = f.related.model_table_name;
			let r_field = f.related.that_field;
			let r_label = f.related.that_label_field;
			joins += " left outer join " + db.escapeId(r_table) + " p" + i + " on t." + db.escapeId(this_field) + "=p" + i + "." + db.escapeId(r_field) + " ";
			//个性显示关联字段的显示，常用于两级及以上关联时，需要显示上层表的字段，以更清晰的表达字段含义
			let table_alias = [{ tablename: r_table, alias: "p" + i }]
			if (f.related.related) {
				for (let j in f.related.related) {
					let related = f.related.related[j];
					let alias = "p" + i + "_" + j;
					let table_name = related.table_name;
					let that_field = related.that_field;
					let this_field = related.this_field;
					table_alias.push({ tablename: table_name, alias: alias });
					joins += " left outer join " + db.escapeId(table_name) + " " + alias + " on p" + i + "." + db.escapeId(this_field) + "=" + alias + "." + db.escapeId(that_field) + " ";
				}
			}
			let select_tpl = "";
			if (f.related.tpl) {
				let label_tpl = f.related.tpl;
				let patt = /\[\w+.\w+\]/g;
				let handled_index = 0;
				let result = [];
				while ((result = patt.exec(label_tpl)) != null) {
					let length = result[0].length;
					let table = result[0].substr(1, result[0].indexOf(".") - 1);
					let field = result[0].substr(result[0].indexOf(".") + 1, length - result[0].indexOf(".") - 2);
					let index = result.index;
					if (index > handled_index) {
						select_tpl += db.escape(label_tpl.substring(handled_index, index)) + ",";
					}
					let find = table_alias.filter(a => a.tablename == table);
					if (find && find.length > 0) {
						select_tpl += find[0].alias + "." + db.escapeId(field) + ",";
					}
					else {
						select_tpl += db.escape(result[0]) + ",";
					}
					handled_index = index + length;
				}
				if (select_tpl.length - 1 > handled_index) {
					select_tpl += db.escape(label_tpl.substring(handled_index)) + ",";
				}
				if (select_tpl) select_tpl = select_tpl.substr(0, select_tpl.length - 1);
				selects += ", concat(" + select_tpl + ") as " + db.escapeId("#" + this_field);
			}
			else {
				selects += ", p" + i + "." + db.escapeId(r_label) + " as " + db.escapeId("#" + this_field);
			}
			let ff = filter.filter(f => { return f.key == "#" + this_field });
			if (ff.length > 0) {
				ff[0].prefix = "p" + i;
				ff[0].key = r_label;
			}
			if (keywords_search_columns) {
				let ksc = keywords_search_columns.filter(f => { return f.name == "#" + this_field });
				if (ksc.length > 0) {
					ksc[0].prefix = "p" + i;
					ksc[0].name = r_label;
				}
			}
			if (searchdata.orderby == this_field) {
				searchdata.orderby_prefix = "p" + i;
				searchdata.orderby = r_label;
			}
			if (tablename == r_table) { //这里关联表为自己，即这里是上级节点的关联字段
				is_leveled = leveled ? true : false;
				leveled_set.parent_field = this_field;
				leveled_set.field = r_field;
			}
		}
		//如果层级模式并且不显示子树，则这里要加上parent_id为空，第三个条件是防止通过父字段查询的时候查不到数据
		if (is_leveled && !leveled_sub_mode && filter.filter(f => f.key == leveled_set.parent_field).length==0) {
			searchdata.filter.push({
				prefix: 't',
				key: leveled_set.parent_field,
				value: ''
			})
		}
		//处理复合查询条件的前缀
		filter.forEach(f => {
			if (f.conjunction && f.children) {
				handle_composite_filter(f, related_fields);
			}
		});
		let sql = "select " + selects + " from " + db.escapeId(tablename) + " t " + joins;
		let result = await db.queryfilter(sql, [], searchdata);
		//层级模式处理
		if (is_leveled) {
			let level_rows = result.rows;
			while (level_rows.length > 0) {
				//无查看权限的列处理
				if (rights.查 && rights.查.hidden_columns) {
					for (let row of result.rows) {
						for (let colname of rights.查.hidden_columns) {
							if (row[colname]) {
								delete row[colname];
							}
						}
					}
				}
				//查下一层的记录
				let ids = '';
				for (level_row of level_rows) {
					ids += (level_row[leveled_set.field] + ",");
				}
				let level_sql = "select " + selects + " from " + db.escapeId(tablename) + " t " + joins +
					" where find_in_set(t." + db.escapeId(leveled_set.parent_field) + ",'" + ids + "' ) and t.is_deleted=0";
				if(searchdata.orderby){
					level_sql += " order by "+(searchdata.orderby_prefix?searchdata.orderby_prefix:"t")+"." + db.escapeId(searchdata.orderby);
				}
				let level_result = await db.query(level_sql, []);
				let rows = level_result.rows;
				for(level_row of level_rows){
					if(!level_row.children) level_row.children=[];
					level_row.children.push(...(rows.filter(r=>r[leveled_set.parent_field]==level_row[leveled_set.field])));
					if(level_row.children.length==0) delete level_row.children;
				}
				level_rows = rows;
			}
		}
		return result;
	}
}

let getinfo = async function (data, login_info, req, res) {
	let datamodel = data.datamodel ? data.datamodel : req.query.datamodel; //数据模型
	let tablename = data.tablename ? data.tablename : req.query.tablename; //表名
	let sql = datamodel ? "select * from sys_datamodel where id=? and is_deleted=0" : "select * from sys_datamodel where tablename=? and is_deleted=0";
	let result = await db.query(sql, [datamodel ? datamodel : tablename]);
	let rows = result.rows;
	if (rows.length == 0) { return (Error('不存在这个datamodel:' + datamodel)); return; }
	tablename = rows[0].tablename;
	let modelname = rows[0].name;
	let config = JSON.parse(rows[0].config);
	result = await db.query("desc ?? ", [tablename]);
	let infos = [];
	let fields = result.rows;
	for (let item of fields) {
		field = item.Field;
		if (field.indexOf("_dropped") > -1) { //被删除的字段前端不显示
			continue;
		}
		let sf = db.systemfields.filter(f => f.name == field);
		if (sf[0] && sf[0].hidden) continue; //is_deleted等隐藏字段
		let cf = config.fields.filter(f => f.name == field);
		let label = sf.length > 0 ? sf[0].label : cf.length > 0 ? cf[0].label : field;
		let defaul = cf.length > 0 ? cf[0].default : undefined;
		let field_type_info = transfer_field_info(item);
		if (cf && cf.type) field_type_info.type = cf.type;
		if (sf[0]) field_type_info.is_sys_field = true;

		if (cf.length > 0 && cf[0].related || sf.length > 0 && sf[0].related) {
			let related = (cf.length > 0 && cf[0].related) ? cf[0].related : sf[0].related;
			infos.push({
				name: field,
				label: label,
				default: defaul,
				related: {
					original: field,
					...related
				},
				...field_type_info
			});
			infos.push({
				name: "#" + field,
				label: related.label ? related.label : "#" + field,
				default: defaul,
				relater: {
					original: field,
					...related
				},
				...field_type_info
			});
		}
		else if (cf.length > 0 && cf[0].select) {
			infos.push({
				name: field,
				label: label,
				default: defaul,
				select: cf[0].select,
				...field_type_info
			});
		}
		else {
			infos.push({
				name: field,
				label: label,
				default: defaul,
				...field_type_info
			})
		}
	}
	let user_id = login_info.user_id;
	if (datamodel) {
		let rights = await rights_handler.get_datamodel_data_rights(datamodel, user_id);
		if (rights.查 && rights.查.hidden_columns) {
			infos = infos.filter(i => !rights.查.hidden_columns.includes(i.name))
		}
		return ({ modelname: modelname, tablename: rights.查 ? tablename : undefined, fields: rights.查 ? infos : [], rights: rights })
	}
	else if (tablename) {
		let rights = await rights_handler.get_table_data_rights(tablename, user_id);
		if (rights.查 && rights.查.hidden_columns) {
			infos = infos.filter(i => !rights.查.hidden_columns.includes(i.name))
		}
		return ({ modelname: modelname, tablename: rights.查 ? tablename : undefined, fields: rights.查 ? infos : [], rights: rights })
	}
	else {
		return (Error('没有提供需要获取信息的datamodel或者tablename'));
	}
}


module.exports = { add, update, del, getrow, getrows, getinfo, set_db };